#!/usr/bin/env node
/**
 * 精简Schema重建（方案X）- 保留 online/offline + target_record_date，删除以下列：
 * total_people / working_people / total_staff / maintenance_time / defect_quantity / rework_quantity / scrap_quantity / downtime_reason
 *
 * - 自动备份 backend/data/pph.sqlite 到 backend/data/backup-YYYYMMDDHHMMSS.sqlite
 * - 事务内完成：创建 v2 -> 复制保留列数据 -> 替换旧表 -> 重建唯一索引
 */
const fs = require('fs');
const path = require('path');
const DatabasePool = require('../utils/dbPool.cjs');

(async () => {
  const dbPath = path.join(__dirname, '..', 'data', 'pph.sqlite');
  const dbPool = new DatabasePool(dbPath, { maxConnections: 1, retryAttempts: 1, healthCheckInterval: 0 });

  function nowStamp(){
    const d=new Date(); const p=n=>String(n).padStart(2,'0');
    return `${d.getFullYear()}${p(d.getMonth()+1)}${p(d.getDate())}${p(d.getHours())}${p(d.getMinutes())}${p(d.getSeconds())}`;
  }

  try{
    // 0) 备份
    const bkName = `backup-${nowStamp()}.sqlite`;
    const dst = path.join(__dirname, '..', 'data', bkName);
    fs.copyFileSync(dbPath, dst);
    console.log('📦 已备份数据库到: ', dst);

    // 1) 执行重建事务
    await dbPool.transaction([
      { sql: `CREATE TABLE pph_records_v2 (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                record_date DATE NOT NULL,
                line_name VARCHAR(50) NOT NULL,
                order_number VARCHAR(100) NOT NULL,
                material_code VARCHAR(50) NOT NULL,
                product_name VARCHAR(200) NOT NULL,
                product_series VARCHAR(100),
                start_time TIME NOT NULL,
                end_time TIME NOT NULL,
                order_quantity INTEGER,
                inbound_quantity INTEGER,
                completed_total_quantity INTEGER,
                to_complete_quantity INTEGER,
                target_record_date DATE,
                completed_quantity INTEGER,
                online_staff INTEGER DEFAULT 0,
                offline_staff INTEGER DEFAULT 0,
                break_minutes DECIMAL(8,2) DEFAULT 0,
                changeover_minutes DECIMAL(8,2) DEFAULT 0,
                abnormal_loss_minutes DECIMAL(8,2) DEFAULT 0,
                net_working_minutes DECIMAL(8,2) DEFAULT 0,
                belt_speed DECIMAL(8,2),
                calculated_pph DECIMAL(8,2),
                target_pph DECIMAL(8,2),
                estimate_pph DECIMAL(8,2),
                efficiency_rate DECIMAL(8,2),
                productivity_index DECIMAL(8,2),
                time_utilization_rate DECIMAL(8,2),
                quality_rate DECIMAL(8,2),
                defect_rate DECIMAL(8,2),
                total_person_hours DECIMAL(8,2),
                effective_person_hours DECIMAL(8,2),
                person_utilization_rate DECIMAL(8,2),
                actual_takt_seconds DECIMAL(8,2),
                is_whole_machine INTEGER,
                abnormality_category VARCHAR(100),
                abnormality_description TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                created_by VARCHAR(50) DEFAULT 'system',
                sync_status VARCHAR(20) DEFAULT 'synced'
              )`, params: [] },
      { sql: `INSERT INTO pph_records_v2 (
                id, record_date, line_name, order_number, material_code, product_name, product_series,
                start_time, end_time,
                order_quantity,inbound_quantity,completed_total_quantity,to_complete_quantity,
                target_record_date,
                completed_quantity, online_staff, offline_staff,
                break_minutes, changeover_minutes, abnormal_loss_minutes,
                net_working_minutes, belt_speed,
                calculated_pph, target_pph, estimate_pph, efficiency_rate,
                productivity_index, time_utilization_rate, quality_rate, defect_rate,
                total_person_hours, effective_person_hours, person_utilization_rate,
                actual_takt_seconds, is_whole_machine,
                abnormality_category, abnormality_description,
                created_at, updated_at, created_by, sync_status
              ) SELECT 
                id, record_date, line_name, order_number, material_code, product_name, product_series,
                start_time, end_time,
                order_quantity,inbound_quantity,completed_total_quantity,to_complete_quantity,
                target_record_date,
                completed_quantity, online_staff, offline_staff,
                break_minutes, changeover_minutes, abnormal_loss_minutes,
                net_working_minutes, belt_speed,
                calculated_pph, target_pph, estimate_pph, efficiency_rate,
                productivity_index, time_utilization_rate, quality_rate, defect_rate,
                total_person_hours, effective_person_hours, person_utilization_rate,
                actual_takt_seconds, is_whole_machine,
                abnormality_category, abnormality_description,
                created_at, updated_at, created_by, sync_status
              FROM pph_records`, params: [] },
      { sql: `DROP TABLE pph_records`, params: [] },
      { sql: `ALTER TABLE pph_records_v2 RENAME TO pph_records`, params: [] },
      { sql: `CREATE UNIQUE INDEX IF NOT EXISTS uq_pph_records_a1 ON pph_records(record_date, line_name, material_code, start_time, end_time)`, params: [] }
    ]);

    // 2) 输出新结构
    const info = await dbPool.query('PRAGMA table_info(pph_records)');
    console.log('✅ 精简Schema重建完成。新列清单:');
    for (const c of info) console.log(` - ${c.name} ${c.type}`);
    process.exit(0);
  }catch(e){
    console.error('❌ 重建失败:', e && (e.message||e));
    process.exit(1);
  }
})();

